﻿using log4net;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace Farkhod.QuickDataViewer
{
    public class DBManager
    {
        private SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder();
        public static string ConnectionString { get; set; }
        private ILog logger = LogManager.GetLogger(typeof(DBManager).Name);

        public static DBManager Instance
        {
            get
            {
                return Nested.DBManager;
            }
        }

        public SqlConnectionStringBuilder ConnectionStringBuilder
        {
            get { return connectionStringBuilder; }

            set
            {
                this.connectionStringBuilder = value;
            }
        }

        private class Nested
        {
            static Nested() { }
            internal static readonly DBManager DBManager = new DBManager();
        }

        public DBManager()
        {
            try
            {
                ConnectionString = ConfigurationManager.ConnectionStrings["SqlServerConnection"].ToString();
                connectionStringBuilder.ConnectionString = ConnectionString;
            }
            catch (Exception ex)
            {
                logger.Error(ex.Message);
                throw;
            }
        }

        public bool SetDBName(string dbName)
        {
            try
            {
                if (string.IsNullOrEmpty(dbName))
                    throw new ArgumentNullException("dbName can not be empty!");

                connectionStringBuilder.InitialCatalog = dbName;
                ConnectionString = connectionStringBuilder.ConnectionString;
                return true;
            }
            catch (Exception ex)
            {
                logger.Error(ex.Message);
                return false;
            }

        }

        public string GetDBName()
        {
            if (connectionStringBuilder != null && !string.IsNullOrEmpty(connectionStringBuilder.InitialCatalog))
                return connectionStringBuilder.InitialCatalog;
            else
                return string.Empty;
        }

        public async Task<List<string>> GetDBs()
        {
            List<string> databases = new List<string>();
            try
            {
                using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
                {
                    await sqlConnection.OpenAsync();
                    using (SqlCommand cmd = sqlConnection.CreateCommand())
                    {
                        cmd.CommandText = "EXEC sp_databases";
                        using (SqlDataReader dataReader = cmd.ExecuteReader())
                        {
                            while (!dataReader.IsClosed)
                            {
                                while (dataReader.Read())
                                    databases.Add(dataReader.GetString(0));

                                if (!dataReader.NextResult())
                                    dataReader.Close();
                            }
                        }
                    }
                }
                return databases;
            }
            catch (Exception ex)
            {
                logger.Error(ex.Message);
                throw;
            }
        }

        public async Task<List<string>> GetTables()
        {
            List<string> tables = new List<string>();
            try
            {
                using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
                {
                    await sqlConnection.OpenAsync();
                    const string TYPE = "BASE TABLE";

                    DataTable dt = await Task.Run(() => sqlConnection.GetSchema("Tables"));
                    foreach (DataRow row in dt.Rows)
                    {
                        string tablename = (string)row[2];
                        string objType = (string)row[3];
                        if (objType.Equals(TYPE))
                            tables.Add(tablename);
                    }
                    tables.Sort();
                }
                return tables;
            }
            catch (Exception ex)
            {
                logger.Error(ex.Message);
                throw ex;
            }
        }

        public async Task<List<string>> GetViews()
        {
            List<string> views = new List<string>();
            try
            {
                using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
                {
                    await sqlConnection.OpenAsync();
                    DataTable dt = await Task.Run(() => sqlConnection.GetSchema("Views"));
                    foreach (DataRow row in dt.Rows)
                    {
                        string tablename = (string)row[2];
                        views.Add(tablename);
                    }
                    views.Sort();
                }
                return views;
            }
            catch (Exception ex)
            {
                logger.Error(ex.Message);
                throw ex;
            }
        }

        public async Task<DataTable> GetDataFromTable(string tvName)
        {
            DataTable data = new DataTable();
            try
            {
                await Task.Factory.StartNew(() =>
                    {
                        using (SqlConnection sqlConnection = new SqlConnection(connectionStringBuilder.ConnectionString))
                        {
                            string commandText = string.Format("SELECT * FROM [{0}]", tvName);

                            using (SqlCommand command = new SqlCommand(commandText, sqlConnection))
                            {
                                command.CommandText = commandText;
                                sqlConnection.Open();

                                using (SqlDataReader dataReader = command.ExecuteReader())
                                {
                                    data.TableName = "myTable";
                                    data.Load(dataReader);
                                    dataReader.Close();
                                }
                            }
                        }
                    });
                return data;
            }
            catch (Exception ex)
            {
                logger.Error(ex.Message);
                throw;
            }
        }


        public void UpdateConnectionStringInConfigFile(string connectionString)
        {
            Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
            config.ConnectionStrings.ConnectionStrings["SqlServerConnection"].ConnectionString = connectionString;
            config.Save(ConfigurationSaveMode.Modified, true);
            ConfigurationManager.RefreshSection("connectionStrings");
        }
    }
}
